/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.*;
import model.Receipt;
import model.Receiptitem;

/**
 *
 * @author Trang
 */
public class ReceipingDAO {

    private Connection con;
    public ProductDAO productDao = new ProductDAO();

    public ReceipingDAO() {
        con = DBFactory.createConnection("onlineshopping");
    }

    public List<Receipt> getAllReceip(int offset, int length) {
        List<Receipt> result = new ArrayList<Receipt>();

        try {
            String sqlst = "SELECT * FROM receipt"
                    + " LIMIT ?, ?";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setInt(1, offset);
            ps.setInt(2, length);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer idReceipt = rs.getInt("idReceipt");
                int totalQuantity = rs.getInt("totalQuantity");
                Date createTime = rs.getDate("createTime");
                double totalAmount = rs.getDouble("totalAmount");

                Receipt tem = new Receipt(idReceipt, createTime, totalQuantity, totalAmount);

                result.add(tem);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

    public List<Receipt> getAllReceip() {
        List<Receipt> result = new ArrayList<Receipt>();

        try {
            String sqlst = "SELECT * FROM receipt";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer idReceipt = rs.getInt("idReceipt");
                int totalQuantity = rs.getInt("totalQuantity");
                Date createTime = rs.getDate("createTime");
                double totalAmount = rs.getDouble("totalAmount");
                Integer idStaff = rs.getInt("Staff_idStaff");
                Integer idSupplier = rs.getInt("Supplier_idSupplier");
                
                Staff staff = (new StaffDAO().getStaffByStaffId(idStaff));
                
                Supplier supplier = (new SupplierDAO().getSupplierFromSupplierID(idSupplier));

                Receipt tem = new Receipt(idReceipt, createTime, totalQuantity, totalAmount, supplier, staff);
                        //(idReceipt, createTime, totalQuantity, totalAmount);

                result.add(tem);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }
    
    public List<Receipt> getAllReceipBetweenDate(Date startDate, Date endDate) {
        List<Receipt> result = new ArrayList<Receipt>();

        try {
            String sqlst = "SELECT * FROM receipt Where createTime Between ? And ?";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setDate(1, new java.sql.Date (startDate.getTime()));
            ps.setDate(2, new java.sql.Date (endDate.getTime()));
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer idReceipt = rs.getInt("idReceipt");
                int totalQuantity = rs.getInt("totalQuantity");
                Date createTime = rs.getDate("createTime");
                double totalAmount = rs.getDouble("totalAmount");
                Integer idStaff = rs.getInt("Staff_idStaff");
                Integer idSupplier = rs.getInt("Supplier_idSupplier");
                
                Staff staff = (new StaffDAO().getStaffByStaffId(idStaff));
                
                Supplier supplier = (new SupplierDAO().getSupplierFromSupplierID(idSupplier));

                Receipt tem = new Receipt(idReceipt, createTime, totalQuantity, totalAmount, supplier, staff);
                        //(idReceipt, createTime, totalQuantity, totalAmount);

                result.add(tem);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }

     public Receipt getReceipFromReceipID(Integer rid) {
        Receipt result = new Receipt();

        try {
            String sqlst = "SELECT * FROM receipt WHERE idReceipt = ?";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setInt(1, rid);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer idReceipt = rs.getInt("idReceipt");
                int totalQuantity = rs.getInt("totalQuantity");
                Date createTime = rs.getDate("createTime");
                double totalAmount = rs.getDouble("totalAmount");
                Integer Supplier_idSupplier = rs.getInt("Supplier_idSupplier");
                Integer Staff_idStaff = rs.getInt("Staff_idStaff");
                
                Staff staff = new StaffDAO().getStaffByStaffId(Staff_idStaff);
                Supplier supplier = new SupplierDAO().getSupplierFromSupplierID(Supplier_idSupplier);
                
                result = new Receipt(idReceipt, createTime, totalQuantity, totalAmount, supplier, staff);
                        //(idReceipt, createTime, totalQuantity, totalAmount);    
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }
    public List<Receiptitem>  getReceiptitemFromReceipID(Integer rid)
    {
        List<Receiptitem> result = new ArrayList<Receiptitem>();
         try {
            String sqlst = "SELECT * FROM receiptitem WHERE Receipt_idReceipt = ?";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setInt(1, rid);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Integer idReceipt = rs.getInt("Receipt_idReceipt");
                int receipQuantity = rs.getInt("receiptQuantity");
                
                double receiptPrice = rs.getDouble("receiptPrice");
                Integer Product_idProduct = rs.getInt("Product_idProduct");
                
                
                Product product = new ProductDAO().getProductFromProductId(Product_idProduct);
                
                Receiptitem item = new Receiptitem(receipQuantity, receiptPrice, product);
                
                result.add(item);
                        //(idReceipt, createTime, totalQuantity, totalAmount);    
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }
    public boolean addReceip(Receipt receipt) {

        boolean result = false;
        List<Receiptitem> receiptitems = new ArrayList<Receiptitem>();

        try {

            String sqlst = "INSERT INTO receipt(createTime, totalAmount, totalQuantity, "
                    + "Staff_idStaff, Supplier_idSupplier) "
                    + "VALUES (?, ?, ?, ?, ?)";
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            Date current = new Date();
            receiptitems = receipt.getReceiptitemList();
            double totalAmount = 0;
            int quantity = 0;
            for (Receiptitem item : receiptitems) {
                totalAmount += item.getReceiptPrice() * item.getReceiptQuantity();
                quantity += item.getReceiptQuantity();
            }
            System.out.println("totalAmount" + totalAmount);
            System.out.println("totalQuantity" + quantity);

            PreparedStatement ps = con.prepareStatement(sqlst);
            if (receipt.getCreateTime() != null) {
                ps.setDate(1, new java.sql.Date(receipt.getCreateTime().getTime()));
            } else {
                ps.setDate(1, new java.sql.Date (current.getTime()));
            }
            ps.setDouble(2, totalAmount);
            ps.setInt(3, quantity);

            ps.setInt(4, receipt.getStaff().getIdStaff());
            ps.setInt(5, receipt.getSupplier().getIdSupplier());
            System.out.println(receipt.getStaff().getIdStaff());
            
            ps.executeUpdate();
            
            String sql1 = "SELECT idReceipt FROM receipt ORDER BY idReceipt DESC limit 1";
            PreparedStatement ps1= con.prepareStatement(sql1);
            ResultSet rs = ps1.executeQuery();
            while(rs.next())
            {
                Integer idReceipt = rs.getInt("idReceipt");
                for (Receiptitem item : receiptitems) {

                    String sql = "INSERT INTO receiptitem(receiptQuantity, receiptPrice, "
                            + "Receipt_idReceipt, Product_idProduct) "
                            + "VALUES (?, ?, ?, ?)";

                    PreparedStatement ps2 = con.prepareStatement(sql);
                    ps2.setInt(1, item.getReceiptQuantity());
                    ps2.setDouble(2, item.getReceiptPrice());
                    ps2.setInt(3, idReceipt);
                    ps2.setInt(4, item.getProduct().getIdProduct());
                    ps2.executeUpdate();
                    System.out.println("Trangpass2");
                }
            }
            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        System.out.println("result" + result);
        return result;
    }

    public boolean addReceip(List<Receiptitem> receiptitems, Integer Staff_idStaff, Integer Supplier_idSupplier, Date createDate) {

        boolean result = false;
        //receiptitems = new ArrayList<Receiptitem>();

        try {

            String sqlst = "INSERT INTO receipt(createTime, totalAmount, totalQuantity, "
                    + "Staff_idStaff, Supplier_idSupplier) "
                    + "VALUES (?, ?, ?, ?, ?)";
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            Date current = new Date();
            //receiptitems = receipt.getReceiptitemList();
            double totalAmount = 0;
            int quantity = 0;
            for (Receiptitem item : receiptitems) {
                totalAmount += item.getReceiptPrice() * item.getReceiptQuantity();
                quantity += item.getReceiptQuantity();
            }
            System.out.println("totalAmount" + totalAmount);
            System.out.println("totalQuantity" + quantity);

            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setDate(1, new java.sql.Date(createDate.getTime()));

            ps.setDouble(2, totalAmount);
            ps.setInt(3, quantity);
            ps.setInt(4, Staff_idStaff);
            ps.setInt(5, Supplier_idSupplier);

            ps.executeUpdate();

            String sql1 = "SELECT idReceipt FROM receipt ORDER BY idReceipt DESC LIMIT 1";
            PreparedStatement ps1 = con.prepareStatement(sql1);

            ResultSet rs = ps1.executeQuery();
            Integer idReceipt;
            while (rs.next()) {
                
                idReceipt = rs.getInt("idReceipt");
                System.out.println("Trangpass1" + idReceipt);
                
                for (Receiptitem item : receiptitems) {
                    System.out.println("Trangpass2" + idReceipt);

                    String sql = "INSERT INTO receiptitem(receiptQuantity, receiptPrice, "
                            + "Receipt_idReceipt, Product_idProduct) "
                            + "VALUES (?, ?, ?, ?)";
                    PreparedStatement ps2 = con.prepareStatement(sql);
                    ps2.setInt(1, item.getReceiptQuantity());
                    ps2.setDouble(2, item.getReceiptPrice());
                    ps2.setInt(3, idReceipt);
                    ps2.setInt(4, item.getProduct().getIdProduct());
                    ps2.executeUpdate();
                }  
            }
            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        System.out.println("result" + result);
        return result;
    }
}
